package com.kingschan.jeasytag.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;

import com.kingschan.util.lang.StringUtil;
/**
 * <pre>
 *@author kingschan
 *date 2012-11-1
 *description:常用DB操作工具类
 *</pre>
 */
public class JdbcTemplete {
	private static Logger log = Logger.getLogger(JdbcTemplete.class);
	/**
	 * 关闭数据库连接
	 * @param rs
	 * @param pre
	 * @param con
	 * @throws Exception
	 */
	public static void closeDB(ResultSet rs ,Statement sm,Connection con)throws Exception{
		if (null!=rs) {
			rs.close();
		}
		if (null!=sm) {
			sm.close();
		}
		if (null!=con) {
			con.close();
		}
	}

	/**
	 * 返回一个map的集合
	 * @param con
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static  List<Map<String, Object>> getListBySql(Connection con,String sql,boolean colse)throws Exception{
		List<Map<String, Object>> lis = new ArrayList<Map<String,Object>>();		
		Statement sm =con.createStatement();
		ResultSet rs=sm.executeQuery(sql);
		ResultSetMetaData  rsmd;
		while (rs.next()) {
			  rsmd =rs.getMetaData();
			  Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 1; i <=rsmd.getColumnCount(); i++) {				
				map.put(rsmd.getColumnName(i), StringUtil.ConvertNullStr(rs.getObject(i)));
			}
			lis.add(map);

		}
		if (colse) {
			JdbcTemplete.closeDB(rs, sm, con);
		}

		return lis;
	}

	/**
	 * 统计行数
	 * @param con
	 * @param sql
	 * @param colse  是否关闭连接
	 * @return
	 * @throws Exception
	 */
	public static int getTotalRows(Connection con,String sql,boolean colse)throws Exception{
		int rows=0;
		Statement sm =con.createStatement();
		ResultSet rs=sm.executeQuery(sql);
		@SuppressWarnings("unused")
		ResultSetMetaData  rsmd;
		while (rs.next()) {
			 rows=rs.getInt(1);			
		}
		if (colse) {
			JdbcTemplete.closeDB(rs, sm, con);
		}
		return rows;
	}
	/**
	 * 根据传入的参数查询数据库返回一个List
	 * @param con
	 * @param sql
	 * @param colse
	 * @param disableColumns 需要过滤的字段
	 * @param Sqlargs
	 * @return
	 * @throws Exception
	 */
	public static List<?> queryForList(Connection con,String sql,boolean colse,Set<String> disableColumns,Object...Sqlargs)throws Exception{
		List<Object[]> lis = new ArrayList<Object[]>();
		PreparedStatement pre =con.prepareStatement(sql);		
		if(null!=Sqlargs){
			for (int i = 1; i <=Sqlargs.length; i++) {
				pre.setObject(i, Sqlargs[i-1]);
				log.debug("参数:"+i+" | "+StringUtil.ConvertNullStr(Sqlargs[i-1]));
			}	
		}
		ResultSet res=pre.executeQuery();
		ResultSetMetaData  rsmd =null;
		List<String> rowslis;
		while (res.next()) {
			rsmd=res.getMetaData();	
			rowslis=new ArrayList<String>();
			for (int i = 1; i <=rsmd.getColumnCount(); i++) {
				//如果当前列是禁用导出列
				if (null!=disableColumns&&disableColumns.contains(rsmd.getColumnName(i))) {
					continue;
				}
				rowslis.add(StringUtil.ConvertNullStr(res.getObject(i)));
			}
			lis.add(rowslis.toArray());
		}
		if (colse) {
			JdbcTemplete.closeDB(res, pre, con);
		}
		return lis;
	}

	/**
	 * 增，删除，改
	 * @param con
	 * @param sql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static int executeRows(Connection con,String sql,Object...objects )throws Exception{
		int impactRows=0;
		PreparedStatement pre =con.prepareStatement(sql);		
		for (int i = 1; i <=objects.length; i++) {
			pre.setObject(i, objects[i-1]);
			log.debug("参数:"+i+" | "+StringUtil.ConvertNullStr(objects[i-1]));
		}		
		impactRows=pre.executeUpdate();
		JdbcTemplete.closeDB(null, pre, con);
		return  impactRows;
	}

	public static void main(String[] args) {
		Set<String> set = new HashSet<String>();
		set.contains("");
	}
}